package dice.sinanya.db.notice;

import dice.sinanya.db.tools.DbUtil;
import dice.sinanya.entity.EntityNotice;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author SitaNya
 * 日期: 2019-08-07
 * 电子邮箱: sitanya@qq.com
 * 维护群(QQ): 162279609
 * 有任何问题欢迎咨询
 * 类说明:
 */
public class SelectNotice {
    private static final Logger log = LogManager.getLogger(SelectNotice.class.getName());


    /**
     * 读取数据库中的骰点历史信息到缓存
     */
    public ArrayList<EntityNotice> FindNoticeByGroupId(long groupId) {
        ArrayList<EntityNotice> notices = new ArrayList<>();
        try (Connection conn = DbUtil.getConnection()) {
            //language=MySQL
            String sql = "select qqId,title,info from test.notice where  groupId=?";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                ps.setLong(1, groupId);
                try (ResultSet set = ps.executeQuery()) {
                    while (set.next()) {
                        notices.add(new EntityNotice(groupId, set.getInt("qqId"), set.getString("title"), set.getString("info")));
                    }
                }
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        return notices;
    }

    public EntityNotice getNoticeByGroupId(long groupId, String title) {
        try (Connection conn = DbUtil.getConnection()) {
            //language=MySQL
            String sql = "select qqId,title,info from test.notice where  groupId=? and title=?";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                ps.setLong(1, groupId);
                ps.setString(2, title);
                try (ResultSet set = ps.executeQuery()) {
                    if (set.next()) {
                        return new EntityNotice(groupId, set.getInt("qqId"), set.getString("title"), set.getString("info"));
                    }
                }
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        return null;
    }

    /**
     * 读取数据库中的骰点历史信息到缓存
     */
    public boolean CheckNoticeInGroup(long groupId, String title) {
        boolean has = false;
        try (Connection conn = DbUtil.getConnection()) {
            //language=MySQL
            String sql = "select count(*) as has from test.notice where title=? and groupId=?";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                ps.setString(1, title);
                ps.setLong(2, groupId);
                try (ResultSet set = ps.executeQuery()) {
                    if (set.next()) {
                        int test = set.getInt("has");
                        return test != 0;
                    }
                }
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        return has;
    }
}
